package com.sduept.nwld.test;

import lombok.extern.slf4j.Slf4j;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@Slf4j
public class ReadFile2DB {
	public static String filepath="E:\\项目管理\\南网大数据\\nwdata\\新建文件夹\\新建文件夹\\";
	String url="jdbc:mysql://10.140.9.61:3306/nwld_source_ems_cim?useUnicode=true&amp;characterEncoding=utf8&amp;user=manager&password=nwld";
	Connection con = null;
	public void getConnection(){
		 try {
			con = DriverManager.getConnection(url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void createTable(String tbname,List<TableData> datas){
		Statement st  = null;
		try {
			st = con.createStatement();
			String sql = genCreateStatementStr(tbname,datas);
			log.info(sql);
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void insert(String tbname,List<TableData> datas) throws SQLException, IOException{
		String sql = genInsertPreparedStatementStr(tbname,
				datas);
		Statement st  = null;
		try {
			st = con.createStatement();
			log.info(sql);
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	private String genInsertPreparedStatementStr(String tableName, List<TableData> datas) {
		// String sql =
		// "INSERT INTO t_cim_model_map(pathName,name,sname,measuretype) VALUES(?,?,?,?)";
		int len = datas.size();
		StringBuilder sb = new StringBuilder("INSERT INTO `" + tableName + "`(");
		int i = 0;
		for (TableData d : datas) {
			sb.append("`"+d.fname+"`");
			if (i != len - 1) {
				sb.append(",");
			}
			i++;
		}
		sb.append(") VALUES(");
		for (i = 0; i < len; i++) {
			sb.append("?");
			if (i != len - 1) {
				sb.append(",");
			}
		}
		sb.append(")");
		return sb.toString();
	}
	public  void test(String fname)throws Exception{
		FileInputStream ins = new FileInputStream(filepath+fname);
		SAXParserFactory saxfac = SAXParserFactory.newInstance();
		SAXParser saxparser = saxfac.newSAXParser();  
		MySaxHandler handler = new MySaxHandler();
		saxparser.parse(ins, handler);  
		ins.close();
	}
	public static void main(String[] args) throws Exception{
		new ReadFile2DB().test("t1.txt");
	}
	private String genCreateStatementStr(String tableName,List<TableData> datas){
		//String sql ="CREATE TABLE `t_t2` (`id` INT NULL,`name` INT NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB"
		int len = datas.size();
		StringBuilder sb = new StringBuilder("CREATE TABLE `"+tableName+"` (");
		int i = 0; 
		for(TableData d : datas){
			sb.append("`"+d.fname+"` ");
			sb.append("VARCHAR(255) NULL");
			if(i != len - 1){
				sb.append(",");
			}
			i++;
		}
		sb.append(")  COLLATE='utf8_general_ci' ENGINE=InnoDB");
		return sb.toString();
	}
	public class MySaxHandler extends DefaultHandler{
		private  StringBuffer tmpValue = null;
		private String name = "test";
		private String format = ".zip";
		private HashMap<String, Boolean> isCreateTableMap = new HashMap<>();
		private HashMap<String, HashMap<String,String>> tb_data = new HashMap<>();
		private HashMap<String,List<TableData>> tb_data_tmp = new HashMap<>();
		@Override
		public void endDocument() throws SAXException {
			log.info("end");
		}

		@Override
		public void endElement(String uri, String localName, String qName)
				throws SAXException {
			log.info("end:" + qName);
			String tbname = null;
			Boolean isCreatedTb = isCreateTableMap.get(qName);
			if(isCreatedTb == null){
				tbname = tb_data_tmp.keySet().iterator().next();
				if(tmpValue != null){
					tb_data_tmp.values().iterator().next().add(new TableData(qName.toLowerCase().replaceAll(":", "_").replaceAll(".", "_"), tmpValue.toString()));
				}
			} else {
				if(!isCreatedTb){
					createTable(qName.toLowerCase().replaceAll(":", "_").replaceAll(".", "_"), tb_data_tmp.get(qName));
				}
				try {
					insert(tbname,tb_data_tmp.get(qName));
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

		@Override
		public void startDocument() throws SAXException {
			log.info("start");
		}

		@Override
		public void startElement(String uri, String localName, String qName,
				Attributes attributes) throws SAXException {
			log.info("start:" + qName);
			tmpValue = new StringBuffer();
			if(attributes != null){
				int alen = attributes.getLength();
				if(alen > 0){
					String rfid = attributes.getValue("rdf:ID");
					if(rfid != null){
						//如果有rfd：id证明是对象
						Boolean ic = isCreateTableMap.get(qName);
						if(ic == null){
							isCreateTableMap.put(qName, false);
						}
						tb_data_tmp = new HashMap<>();
						List<TableData> tds = new ArrayList<>();
						tds.add(new TableData("rdf_id", rfid));
						tb_data_tmp.put(qName, tds);
					} else {
						String rfresource = attributes.getValue("rdf:resource");
						if(rfresource != null){
							List<TableData> tds = tb_data_tmp.get(qName);
							tds.add(new TableData(qName.replaceAll(":", "_").replaceAll(".", "_").toLowerCase()+"_rdf_resource", rfresource));
						}
					}
				}
			}
		
			
			
		}
		@Override
		public void characters(char[] ch, int start, int length)
				throws SAXException {
			if(ch != null && ch.length > 0){
				if(tmpValue == null)
					tmpValue = new StringBuffer();
				tmpValue.append(new String(ch,start,length));
				
			} else {
				tmpValue = null;
			}
		}
		
	}
	public class TableData{
		public String fname;
		public String value;
		public TableData(String f,String v){
			fname = f;
			value = v;
		}
	}
}
